package com.quanjiaolian.playground.serviceimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.quanjiaolian.DB.DataBase;
import com.quanjiaolian.bean.PlaygroundSub;
import com.quanjiaolian.playground.service.PlaygroundService;
import com.quanjiaolian.tools.LocationUtils;

public class PlaygroundServiceImpl implements PlaygroundService {

	DataBase db = new DataBase();

	/**
	 * 声明数据库连接实例
	 */
	// 定义一个Connection 用来连接数据库
	Connection conn = null;
	// 声明PreparedStatement实例
	PreparedStatement pstmt = null;
	// 用于存放返回结果的数据集
	ResultSet rs = null;

	@SuppressWarnings("static-access")
	public int pgSubmit(PlaygroundSub pgs) {
		String sql = "insert into sub_pg_info (sub_pg_name,sub_pg_person,sub_phone,sub_time,sub_stat,sub_result) values (?,?,?,?,?,?)";
		int i = 0;
		conn = db.getconn();
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, pgs.getSub_pg_name());
			pstmt.setString(2, pgs.getSub_pg_person());
			pstmt.setString(3, pgs.getSub_phone());
			pstmt.setTimestamp(4, new java.sql.Timestamp(System
					.currentTimeMillis()));
			pstmt.setInt(5, pgs.getSub_stat());
			pstmt.setInt(6, pgs.getSub_result());
			i = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.close(conn, pstmt, rs);
		}
		return i;
	}

	@SuppressWarnings("static-access")
	public List<Object> queryAllPGInfo(int firstSize, int maxSize,
			String latitude, String longitude, String pg_name) {
		List<Object> list = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer();
		sql
				.append("SELECT pgi.pg_id,pgi.pg_name,pgi.pg_addr,pgi.pg_img1,pgi.pg_latitude,pgi.pg_longitud ");
		sql.append("FROM playground_info pgi ");
		if (pg_name != null && !"".equals(pg_name)) {
			sql.append("WHERE pgi.pg_name LIKE '%" + pg_name + "%' ");
		}
		sql.append("ORDER BY pgi.pg_id ");
		sql.append("LIMIT " + (firstSize - 1) * maxSize);
		sql.append("," + maxSize);
		
		System.out.println("queryAllPGInfo sql is :"+sql.toString());
		conn = db.getconn();
		try {
			pstmt = conn.prepareStatement(sql.toString());
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("pg_id", rs.getInt(1));
				map.put("pg_name", rs.getString(2));
				map.put("pg_addr", rs.getString(3));
				map.put("pg_img", rs.getString(4));
				map.put("distance", LocationUtils.distance(Double
						.valueOf(longitude.trim()), Double.valueOf(latitude
						.trim()), Double.valueOf(rs.getString(6).trim()),
						Double.valueOf(rs.getString(5).trim())));
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.close(conn, pstmt, rs);
		}

		return list;
	}

	@SuppressWarnings("static-access")
	public List<Object> queryNearByUser(int firstSize, int maxSize,
			String latitude, String longitude, double minLat, double minLng,
			double maxLat, double maxLng) {
		List<Object> list = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT pgi.pg_id,pgi.pg_name,pgi.pg_addr,pgi.pg_img1,pgi.pg_latitude,pgi.pg_longitud ");
		sql.append("FROM playground_info pgi ");
		sql.append("WHERE pgi.pg_longitud > ? ");
		sql.append("AND pgi.pg_longitud < ? ");
		sql.append("AND pgi.pg_latitude > ? ");
		sql.append("AND pgi.pg_latitude < ? ");
		sql.append("ORDER BY ACOS(SIN((? * 3.1415) / 180 ) * SIN((pgi.pg_latitude * 3.1415) / 180 )");
		sql.append("+COS((? * 3.1415) / 180 ) * COS((pgi.pg_latitude * 3.1415) / 180 )");
		sql.append("*COS((? * 3.1415) / 180 - (pgi.pg_longitud * 3.1415) / 180 ) )");
		sql.append("* 6380 asc LIMIT ?,?");
		conn = db.getconn();
		try {
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setDouble(1,minLng);
			pstmt.setDouble(2,maxLng);
			pstmt.setDouble(3,minLat);
			pstmt.setDouble(4,maxLng);
			pstmt.setString(5,latitude);
			pstmt.setString(6,latitude);
			pstmt.setString(7,longitude);
			pstmt.setInt(8,(firstSize - 1) * maxSize);
			pstmt.setInt(9,maxSize);
			rs = pstmt.executeQuery();
			while(rs.next()){
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("pg_id", rs.getInt(1));
				map.put("pg_name", rs.getString(2));
				map.put("pg_addr", rs.getString(3));
				map.put("pg_img", rs.getString(4));
				map.put("distance", LocationUtils.distance(Double
						.valueOf(longitude.trim()), Double.valueOf(latitude
						.trim()), Double.valueOf(rs.getString(6).trim()),
						Double.valueOf(rs.getString(5).trim())));
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.close(conn, pstmt, rs);
		}
		return list;
	}
	
	@SuppressWarnings("static-access")
	public List<Object> queryPGDetailById(int pg_id){
		List<Object> list = new ArrayList<Object>();
		String sql = "SELECT pgi.pg_name,pgi.pg_addr,pgi.pg_img1,pgi.pg_img2,pgi.pg_img3,pgi.pg_img4,pgi.pg_loc_img,pgi.pg_tel FROM playground_info pgi WHERE pgi.pg_id = ?";
		conn = db.getconn();
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,pg_id);
			rs = pstmt.executeQuery();
			rs.next();
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("pg_name",rs.getString(1));
			map.put("pg_addr",rs.getString(2));
			map.put("pg_img1",rs.getString(3));
			map.put("pg_img2",rs.getString(4));
			map.put("pg_img3",rs.getString(5));
			map.put("pg_img4",rs.getString(6));
			map.put("pg_loc_img",rs.getString(7));
			map.put("pg_tel",rs.getString(8));
			list.add(map);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.close(conn, pstmt, rs);
		}
		
		return list;
	}
	
	public static void main(String[] args) {
		PlaygroundServiceImpl pgs = new PlaygroundServiceImpl();
		System.out.println(pgs.queryPGDetailById(14758));
	}


}
